# 读数据
import json
import logging.handlers
import os

import pymysql

from config import BASE_DIR
from bs4 import BeautifulSoup


def read_json(filename, key):
    arr = []
    # 拼接文件完整路径
    file_path = BASE_DIR + os.sep + 'data' + os.sep + filename
    # 读取数据文件
    with open(file_path, 'r', encoding='utf-8') as f:
        for data in json.load(f).get(key):
            arr.append(tuple(data.values())[1:])
    return arr


class GetLog:
    log = None

    @classmethod
    def get_log(cls):
        cls.log = None
        if cls.log is None:
            # 1.获取日志器
            cls.log = logging.getLogger()
            # 设置日志级别info
            cls.log.setLevel(logging.INFO)
            # 2.获取处理器
            filepath = BASE_DIR + os.sep + 'log' + os.sep + 'p2p.log'
            tf = logging.handlers.TimedRotatingFileHandler(filename=filepath, when='midnight', interval=1
                                                           , backupCount=3, encoding='utf-8')
            # 3.获取格式器
            fmt = "%(asctime)s %(levelname)s [%(filename)s(%(funcName)s:%(lineno)d)] - %(message)s"
            fm = logging.Formatter(fmt)
            # 4.将格式器添加到处理器
            tf.setFormatter(fm)
            # 5.将处理器添加到日志器
            cls.log.addHandler(tf)
        return cls.log


def parser_html(result):
    # 1.提取html
    html = result.json().get('description').get('form')

    # 2.获取bs对象
    bs = BeautifulSoup(html, 'html.parser')
    # 3.提取url
    url = bs.form.get('action')
    data = {}
    # 4.查找input标签
    for input in bs.find_all('input'):
        data[input.get('name')] = input.get('value')
    return url, data


# 连接数据库工具
def conn_mysql(sql):
    cursor = None
    conn = None
    try:
        # 1、获取连接对象
        conn = pymysql.Connection(host='121.43.169.97', user='root', password='Itcast_p2p_20191228',
                                  database='czbk_member', port=3306,
                                  charset='utf8', autocommit=True)
        # 2、获取游标
        cursor = conn.cursor()
        # 3、执行sql语句
        cursor.execute(sql)
        # 判断执行语句是否为查询语句，
        if sql.split()[0].lower() == 'select':
            # 如果为查询语句，返回
            return cursor.fetchall()
        else:
            # 不为查询语句，返回受影响的行数
            return "受影响的函数为{}".format(cursor.rownumber)

    except Exception as e:
        GetLog().get_log().error(e)
        raise
    finally:
        # 4、关闭游标
        cursor.close()
        # 5、关闭连接
        conn.close()


# 清除数据库
def clear_data():
    sql1 = """
    delete i.* from mb_member_info i INNER JOIN mb_member m on i.member_id=m.id where m.phone in ("13600001111","13600001112","13600001113","13600001114")
    """
    conn_mysql(sql1)
    sql2 = """
    delete l.* from mb_member_login_log l INNER JOIN mb_member m on l.member_id=m.id where m.phone in ("13600001111","13600001112","13600001113","13600001114")
    """
    conn_mysql(sql2)
    sql3 = """
    delete from mb_member_register_log where phone in ("13600001111","13600001112","13600001113","13600001114")
    """
    conn_mysql(sql3)
    sql4 = """
    delete from mb_member where phone in ("13600001111","13600001112","13600001113","13600001114")
    """
    conn_mysql(sql4)


if __name__ == '__main__':
    # arr = read_json('register_login.json', 'img_code')
    # print(arr)
    # arr = read_json('register_login.json', 'msg_code')
    # print(arr)
    # arr = read_json('register_login.json', 'register')
    # print(arr)
    # GetLog.get_log().info('信息级别测试')
    sql = 'SELECT * from mb_number'
    print(conn_mysql(sql))
